import random

from conf import *
from database import Mysql
# 1、更新MRP方案的创建时间为销售订单的单据时间的5分钟以内
def update_mrp(db):

    # 抽取时间变量（毫秒级）
    # max_random_5min = 5 * 60 * 1000  # 5分钟毫秒数
    random_0_5min = random.randint(0, 5) * 60 * 1000  # 0-5分钟随机时间
    random_5_10min = 5 * 60 * 1000 + random.randint(0, 5) * 60 * 1000  # 5-10分钟随机时间
    random_10_15min = 10 * 60 * 1000 + random.randint(0, 5) * 60 * 1000  # 10-15分钟随机时间
    random_15_20min = 15 * 60 * 1000 + random.randint(0, 5) * 60 * 1000  # 15-20分钟随机时间
    tenant_id = TenantId
    purchase_assignee_name = purchase_dept_staff["name"]
    produce_assignee_name = produce_dept_staff["name"]
    purchase_creator_id = purchase_dept_staff["id"]
    produce_creator_id = produce_dept_staff["id"]

    # 1、更新MRP方案的创建时间
    # 更新MRP方案的创建时间为销售订单(sale_order)的单据时间的5分钟以内
    sql1 = """
        UPDATE mrp_project mp
        JOIN (
            SELECT
                mpor.project_id,
                MAX(so.order_time) AS max_order_time,
                MAX(so.order_time) + %s AS new_create_time
            FROM mrp_project_order_relation mpor
            JOIN sale_order so ON mpor.sale_order_id = so.id
            GROUP BY mpor.project_id
        ) AS source ON mp.id = source.project_id
        SET mp.create_time = source.new_create_time, mp.creator_id = %s, mp.creator_name = %s
    """
    count1 = db.update(sql1, (random_0_5min, sales_dept_staff["id"], sales_dept_staff["name"]))
    print(f"SQL1 更新MRP方案创建时间: 影响 {count1} 行")

    # 2、更新MRP方案-生产计划
    sql2 = """
        UPDATE mrp_produce_plan pp
        JOIN sale_order so ON pp.sale_id = so.id
        SET
            pp.create_time = so.create_time + %s,
            pp.submit_time = so.create_time + %s,
            pp.assignee_time = so.create_time + %s,
            pp.submit_id = %s,
            pp.submit_name = %s,
            pp.assignee_name = %s
    """
    count2 = db.update(sql2, (random_5_10min, random_10_15min, random_15_20min, sales_dept_staff["id"], sales_dept_staff["name"], produce_dept_staff["name"]))
    print(f"SQL2 更新MRP生产计划: 影响 {count2} 行")

    # 接下来根据mrp方案来更新时间   
    # 3、更新MRP方案-采购计划
    sql3 = """
        UPDATE mrp_purchase_plan pp
        JOIN mrp_project p ON pp.project_id = p.id
        SET
            pp.plan_time = UNIX_TIMESTAMP(DATE(FROM_UNIXTIME(p.create_time / 1000))) * 1000,
            pp.create_time = p.create_time + %s,
            pp.submit_time = p.create_time + %s,
            pp.assignee_time = p.create_time + %s,
            pp.submit_id = %s,
            pp.submit_name = %s,
            pp.assignee_name = %s
    """
    count3 = db.update(sql3, (random_0_5min, random_5_10min, random_10_15min, sales_dept_staff["id"], sales_dept_staff["name"], purchase_dept_staff["name"]))
    print(f"SQL3 更新MRP采购计划: 影响 {count3} 行")

    # 4、更新采购计划审核操作时间
    sql4 = """
        UPDATE ecloud_bpmn.act_bpmn_process_log log
        JOIN mrp_purchase_plan mpp ON log.process_instance_id = mpp.process_id
        SET
            log.create_time = CASE 
                WHEN log.operate_name = '发起审核' THEN mpp.submit_time 
                WHEN log.operate_name = '审核' THEN mpp.assignee_time 
                ELSE log.create_time 
            END,
            log.last_update_time = CASE 
                WHEN log.operate_name = '发起审核' THEN mpp.submit_time 
                WHEN log.operate_name = '审核' THEN mpp.assignee_time 
                ELSE log.last_update_time 
            END
        WHERE log.tenant_id = %s and log.operate_name IN ('发起审核', '审核')
    """
    count4 = db.update(sql4, (tenant_id,))
    print(f"SQL4 更新采购计划审核时间: 影响 {count4} 行")

    # 5、更新生产计划审核操作时间
    sql5 = """
        UPDATE ecloud_bpmn.act_bpmn_process_log log
        JOIN mrp_produce_plan mpp ON log.process_instance_id = mpp.process_id
        SET
            log.create_time = CASE 
                WHEN log.operate_name = '发起审核' THEN mpp.submit_time 
                WHEN log.operate_name = '审核' THEN mpp.assignee_time 
                ELSE log.create_time 
            END,
            log.last_update_time = CASE 
                WHEN log.operate_name = '发起审核' THEN mpp.submit_time 
                WHEN log.operate_name = '审核' THEN mpp.assignee_time 
                ELSE log.last_update_time 
            END
        WHERE log.tenant_id = %s and log.operate_name IN ('发起审核', '审核')
    """
    count5 = db.update(sql5, (tenant_id,))
    print(f"SQL5 更新生产计划审核时间: 影响 {count5} 行")

    # 6、更新采购计划审核人姓名
    sql6 = """
        UPDATE ecloud_bpmn.act_bpmn_process_log log
        set
            log.creator_id = %s,
            log.creator_name = %s
        where
            tenant_id = %s
            and log.operate_name IN ('发起审核', '审核')
            and log.business_type = 'MRP采购计划订单'
    """
    count6 = db.update(sql6, (purchase_dept_staff["id"], purchase_dept_staff["name"], tenant_id))
    print(f"SQL6 更新采购计划审核人信息: 影响 {count6} 行")

    # 7、更新生产计划审核人姓名
    sql7 = """
        UPDATE ecloud_bpmn.act_bpmn_process_log log
        set
            log.creator_id = %s,
            log.creator_name = %s
        where
            tenant_id = %s
            and log.operate_name IN ('发起审核', '审核')
            and log.business_type = 'MRP生产计划'
    """
    count7 = db.update(sql7, (produce_dept_staff["id"], produce_dept_staff["name"], tenant_id))
    print(f"SQL7 更新生产计划审核人信息: 影响 {count7} 行")

    # 8、更新库存收发明细表
    sql8 = """
        UPDATE wms_stock_log l
        JOIN wms_inbound_form f ON l.bill_code = f.bill_code
        SET l.operate_time = f.bill_time
        WHERE l.bill_code IS NOT NULL
    """
    count8 = db.update(sql8)
    print(f"SQL8 更新库存收发明细表: 影响 {count8} 行")

    db.commit()
